package com.infosys.cabplanningdao.trip.dao.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.infosys.cabplanning.common.domain.CriteriaDTO;
import com.infosys.cabplanning.common.domain.Employee;
import com.infosys.cabplanning.common.domain.Trip;
import com.infosys.cabplanning.common.domain.TripDetails;
import com.infosys.cabplanningdao.trip.dao.TripDAO;

public class TripDAOImpl extends JdbcDaoSupport implements TripDAO {

	@Override
	public void saveTripDetails(final List<TripDetails> tripDetailsList) {
		System.out.println("Saving data in trip_details");
		String sql = "INSERT INTO hackathon.trip_details "
				+ "(schedule_id, cab_id, driver_id) VALUES (?, ?, ?)";
		
		System.out.println("tripDetailsList.size "+tripDetailsList.size());
		
		try {
			getJdbcTemplate().batchUpdate(sql,
					new BatchPreparedStatementSetter() {

						@Override
						public void setValues(PreparedStatement ps, int i)
								throws SQLException {
							TripDetails tripDetails = tripDetailsList.get(i);
							ps.setInt(1, tripDetails.getSchedule()
									.getScheduleId());
							ps.setInt(2, tripDetails.getCab().getCabId());
							ps.setInt(3, tripDetails.getDriver().getDriverId());
						}

						@Override
						public int getBatchSize() {
							return tripDetailsList.size();
						}
					});
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("Saved data in trip_details");
	}

	@Override
	public List<TripDetails> getTripDetails(CriteriaDTO criteriaDTO) {
		String sql = "SELECT sch.schedule_id, sch.schedule_start_time, "
				+ "sch.schedule_date, cab.cab_id, cab.type_id, ctyp.name, ctyp.capacity,"
				+ "cab.registration, drv.driver_id, drv.dl_no, "
				+ "drv.first_name, drv.last_name, drv.phone FROM hackathon.schedule sch, hackathon.cab cab, hackathon.cab_type ctyp, hackathon.driver drv, hackathon.trip_details td"
				+ "WHERE td.schedule_id = sch.schedule_id AND td.cab_id = cab.cab_id AND td.driver_id = driver.driver_id";
		
		List<Object> parameterList = new ArrayList<Object>();
		if (criteriaDTO.getSchedule() != null) {
			sql = sql + "AND sch.schedule_id = ?";
			parameterList.add(criteriaDTO.getSchedule().getScheduleId());
		} else {
			//TODO Throw Exception here
		}
		
		if (criteriaDTO.getCab() != null) {
			sql = sql + "AND cab.cab_id = ?";
			parameterList.add(criteriaDTO.getSchedule().getScheduleId());
		}
		
		if (criteriaDTO.getSchedule() != null) {
			sql = sql + "AND drv.drivere_id = ?";
			parameterList.add(criteriaDTO.getSchedule().getScheduleId());
		}
		
		if (!parameterList.isEmpty()) {
			List<TripDetails> tripDetailsList = getJdbcTemplate().query(sql,
					parameterList.toArray(), new TripDetailsRowMapper());
			return tripDetailsList;
		} else {
			//TODO Throw
			return new ArrayList<TripDetails>();
		}
	}

	@Override
	public void saveTrip(final List<Trip> tripList) {
		System.out.println("Saving data in trip");
		String sql = "INSERT INTO hackathon.trip "
				+ "(trip_details_id, schedule_id, employees, status) VALUES (?, ?, ?, ?)";
		
		getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
		@Override
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			Trip trip = tripList.get(i);
			ps.setInt(1, trip.getTripDetails().getTripDetailsId());
			ps.setInt(2, trip.getSchedule().getScheduleId());
			StringBuilder sb = new StringBuilder();
			for(Employee emp : trip.getEmpList()) {
				sb.append(emp.getEmpId()+",");
			}
			ps.setString(i, sb.toString());
		}
	 
		@Override
		public int getBatchSize() {
			return tripList.size();
		}
	  });
	System.out.println("Saved data in trip");
	}

	@Override
	public List<Trip> getTrips(CriteriaDTO criteriaDTO) {
		String sql = "SELECT sch.schedule_id, sch.schedule_start_time, "
				+ "sch.schedule_date, cab.cab_id, cab.type_id, ctyp.name, ctyp.capacity,"
				+ "cab.registration, drv.driver_id, drv.dl_no, "
				+ "drv.first_name, drv.last_name, drv.phone "
				+ "FROM schedule sch, cab cab, cab_type ctyp, driver drv, trip_details td"
				+ "WHERE td.schedule_id = sch.schedule_id AND td.cab_id = cab.cab_id AND td.driver_id = driver.driver_id"
				+ "AND employee";
		
		List<Object> parameterList = new ArrayList<Object>();
		if (criteriaDTO.getSchedule() != null) {
			sql = sql + "AND sch.schedule_id = ?";
			parameterList.add(criteriaDTO.getSchedule().getScheduleId());
		} else {
			//TODO Throw Exception here
		}
		
		if (criteriaDTO.getCab() != null) {
			sql = sql + "AND cab.cab_id = ?";
			parameterList.add(criteriaDTO.getSchedule().getScheduleId());
		}
		
		if (criteriaDTO.getSchedule() != null) {
			sql = sql + "AND drv.drivere_id = ?";
			parameterList.add(criteriaDTO.getSchedule().getScheduleId());
		}
		
		if (!parameterList.isEmpty()) {
			List<Trip> tripDetailsList = getJdbcTemplate().query(sql,
					parameterList.toArray(), new TripRowMapper());
			return tripDetailsList;
		} else {
			//TODO Throw
			return new ArrayList<Trip>();
		}
	}

}
